Data Analysis of produts sold in different Brazilian cities

0.0 Import the necessary libraries

import os
import pandas as pd

1.0 Go through all files from folder "Sales"

file_list = os.listdir(r"C:\Users\beatl\OneDrive\Área de Trabalho\MiniCursoPython\Sales")
display(file_list)

['Devolucoes - Belo Horizonte.csv',
 'Devolucoes - Curitiba.csv',
 'Devolucoes - Fortaleza.csv',
 'Devolucoes - Goiás.csv',
 'Devolucoes - Porto Alegre.csv',
 'Devolucoes - Recife.csv',
 'Devolucoes - Rio de Janeiro.csv',
 'Devolucoes - Salvador.csv',
 'Devolucoes - São Paulo.csv',
 'Vendas - Belo Horizonte.csv',
 'Vendas - Curitiba.csv',
 'Vendas - Fortaleza.csv',
 'Vendas - Goiás.csv',
 'Vendas - Porto Alegre.csv',
 'Vendas - Recife.csv',
 'Vendas - Rio de Janeiro.csv',
 'Vendas - Salvador.csv',
 'Vendas - São Paulo.csv']

1.2 Importing the sales dataset

total_table = pd.DataFrame()
for file in file_list:
  # if has "Sales" in tne file name, so
  if "Vendas" in file:
    # importar o file
    table = pd.read_csv(f"C:/Users/beatl/OneDrive/Área de Trabalho/MiniCursoPython/Sales/{file}")
    total_table = total_table.append(table)

display(total_table)

	Unnamed: 0	SKU	Produto	Quantidade Vendida	Primeiro Nome	Sobrenome	Data	Loja	Preco Unitario	Unnamed: 8
0	17	HL4379	Televisão	2	Carolina	Alfradique	2/25/2018	Belo Horizonte	2500	NaN
1	25	HL4379	Televisão	1	Danilo	Rubim	2/20/2018	Belo Horizonte	2500	NaN
2	27	HL1918	iPhone	5	Bernard	Pedrosa	7/7/2018	Belo Horizonte	5300	NaN
3	54	HL1918	iPhone	5	Lucas	Lemos	12/26/2018	Belo Horizonte	5300	NaN
4	67	HL8851	Notebook	5	Bernardo	Botelho	6/8/2018	Belo Horizonte	3500	NaN
...	...	...	...	...	...	...	...	...	...	...
2221	9981	HL7348	SmartWatch	4	João	Junior	4/14/2018	São Paulo	1400	NaN
2222	9984	HL1918	iPhone	4	Itai	Puntel	5/2/2018	São Paulo	5300	NaN
2223	9985	HL1918	iPhone	1	Guilherme	Vianna	3/18/2018	São Paulo	5300	NaN
2224	9991	HL7348	SmartWatch	5	Antônio	Soares	11/21/2018	São Paulo	1400	NaN
2225	9996	HL4379	Televisão	5	Caio	Moraes	1/22/2018	São Paulo	2500	NaN

9999 rows × 10 columns

2.0 Data wrangling

2.1 Changing the name of the columns from Portuguese to English

total_table = total_table.rename(columns={'Produto': 'Product', 'Quantidade Vendida': 'Quantity Sold', 'Primeiro Nome': 'FirstName', \
                      'Sobrenome': 'LastName', 'Data':'Date', 'Loja':'Store', 'Preco Unitario':'Unit Price'})
display(total_table)

	Unnamed: 0	SKU	Product	Quantity Sold	FirstName	LastName	Date	Store	Unit Price	Unnamed: 8
0	17	HL4379	Televisão	2	Carolina	Alfradique	2/25/2018	Belo Horizonte	2500	NaN
1	25	HL4379	Televisão	1	Danilo	Rubim	2/20/2018	Belo Horizonte	2500	NaN
2	27	HL1918	iPhone	5	Bernard	Pedrosa	7/7/2018	Belo Horizonte	5300	NaN
3	54	HL1918	iPhone	5	Lucas	Lemos	12/26/2018	Belo Horizonte	5300	NaN
4	67	HL8851	Notebook	5	Bernardo	Botelho	6/8/2018	Belo Horizonte	3500	NaN
...	...	...	...	...	...	...	...	...	...	...
2221	9981	HL7348	SmartWatch	4	João	Junior	4/14/2018	São Paulo	1400	NaN
2222	9984	HL1918	iPhone	4	Itai	Puntel	5/2/2018	São Paulo	5300	NaN
2223	9985	HL1918	iPhone	1	Guilherme	Vianna	3/18/2018	São Paulo	5300	NaN
2224	9991	HL7348	SmartWatch	5	Antônio	Soares	11/21/2018	São Paulo	1400	NaN
2225	9996	HL4379	Televisão	5	Caio	Moraes	1/22/2018	São Paulo	2500	NaN

9999 rows × 10 columns

2.2 Checking the missing values

(total_table.isna().sum() / len(total_table['SKU'])*100).round(2)

Unnamed: 0         0.0
SKU                0.0
Product            0.0
Quantity Sold      0.0
FirstName          0.0
LastName           0.0
Date               0.0
Store              0.0
Unit Price         0.0
Unnamed: 8       100.0
dtype: float64

2.3 Droping the missing values

total_table = total_table.drop(['Unnamed: 0', "Unnamed: 8"], axis=1)
display(total_table)

	SKU	Product	Quantity Sold	FirstName	LastName	Date	Store	Unit Price
0	HL4379	Televisão	2	Carolina	Alfradique	2/25/2018	Belo Horizonte	2500
1	HL4379	Televisão	1	Danilo	Rubim	2/20/2018	Belo Horizonte	2500
2	HL1918	iPhone	5	Bernard	Pedrosa	7/7/2018	Belo Horizonte	5300
3	HL1918	iPhone	5	Lucas	Lemos	12/26/2018	Belo Horizonte	5300
4	HL8851	Notebook	5	Bernardo	Botelho	6/8/2018	Belo Horizonte	3500
...	...	...	...	...	...	...	...	...
2221	HL7348	SmartWatch	4	João	Junior	4/14/2018	São Paulo	1400
2222	HL1918	iPhone	4	Itai	Puntel	5/2/2018	São Paulo	5300
2223	HL1918	iPhone	1	Guilherme	Vianna	3/18/2018	São Paulo	5300
2224	HL7348	SmartWatch	5	Antônio	Soares	11/21/2018	São Paulo	1400
2225	HL4379	Televisão	5	Caio	Moraes	1/22/2018	São Paulo	2500

9999 rows × 8 columns

2.4 Checking again the missing values

(total_table.isna().sum() / len(total_table['SKU'])*100).round(2)

SKU              0.0
Product          0.0
Quantity Sold    0.0
FirstName        0.0
LastName         0.0
Date             0.0
Store            0.0
Unit Price       0.0
dtype: float64

2.5 Cheking the duplicate cases

(total_table.duplicated(keep=False).sum() / len(total_table['SKU'])*100).round(2)

0.0

3.0 Calculate the product that sold the most in quantity

products_table = total_table.groupby('Product').sum()
products_table = products_table[["Quantity Sold"]].sort_values(by="Quantity Sold", ascending=False)
display(products_table)

	Quantity Sold
Product
iPhone	8974
Televisão	5931
Notebook	3249
Android	3183
SmartWatch	2980
Tablet	2921
Câmera	2805

3.1 Ploting Quantity Sold per Product chart

import plotly.express as px

chart = px.bar(products_table, x=products_table.index, y='Quantity Sold', text_auto='.2s')
chart.show()

4.0 Calculate the product that sold the most in revenue

total_table['Revenue'] = total_table['Quantity Sold'] * total_table['Unit Price']

revenues_table = total_table.groupby('Product').sum()
revenues_table = revenues_table[["Revenue"]].sort_values(by="Revenue", ascending=False)
display(revenues_table)

	Revenue
Product
iPhone	47562200
Televisão	14827500
Notebook	11371500
Android	10822200
Câmera	5890500
Tablet	4673600
SmartWatch	4172000

4.1 Ploting Revenue per Product chart

import plotly.express as px

chart = px.bar(revenues_table, x=revenues_table.index, y='Revenue', text_auto='.2s')
chart.show()

5.0 Calculating the city(stores) that sold the most in revenue

stores_table = total_table.groupby('Store').sum()
stores_table = stores_table[['Revenue']]
display(stores_table)

	Revenue
Store
Belo Horizonte	6480300
Curitiba	7060500
Fortaleza	14087900
Goiás	7441800
Porto Alegre	6868600
Recife	7303000
Rio de Janeiro	14867800
Salvador	13111300
São Paulo	22098300

5.1 Ploting Revenue per Store chart

import plotly.express as px

chart = px.bar(stores_table, x=stores_table.index, y='Revenue', text_auto='.2s')
chart.show()